<?php if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class Api_model extends CI_Model {
	public function __construct() {
		parent::__construct();
	}


    public function get_users(){
    	$q = $this->db->query("SELECT au.user_id 
                  , au.company_id 
    							, au.user_name 
    							, au.first_name 
    							, au.last_name 
    							, au.pwd 
                  , au.email
    							, au.enable_flag
    							, aur.start_date 
    							, aur.end_date 
    						  FROM app_users au INNER JOIN app_user_roles aur ON au.user_id = aur.user_id WHERE aur.role_id = 31 AND aur.enable_flag = 'Y' ");
    	return $q->result();
    }

    public function gen_table($data){
     $this->db->query('call ubs_populate_bill_prog_prc(?)',$data);
     $this->db->query('call ubs_populate_coll_prog_prc(?)',$data);
    }
    
    public function get_mv_billing(){
      $q = $this->db->query("SELECT * FROM mv_billing_prog_v");
      return $q->result();
    }

     public function insert_user($data){
      $this->db->query("call app_users_ins(?,?,?,?,?,?,?,?,?,@id,@err,@msg)",$data);
      $que = $this->db->query("SELECT @id as id , @err as error , @msg as msg");
      return $que->result();
    }

     public function insert_role_access($data){
      $this->db->query("call app_user_roles_ins(?,?,?,?,?,?,@err,@msg)",$data);
      $que = $this->db->query("SELECT @err as error , @msg as msg");
      return $que->result();
    }
    public function get_user_roles(){

    }
    public function get_mv_collection(){
      $q = $this->db->query("SELECT * FROM mv_collection_prog");
      return $q->result();
    }

    public function get_arrears(){
       $sql="SELECT tbl.area,
        COUNT(if(tbl.a_count='2',1,NULL)) as c2,
        COUNT(if(tbl.a_count='3',1,NULL)) as c3,
        COUNT(if(tbl.a_count='4',1,NULL)) as c4,
        COUNT(if(tbl.a_count>4,1,NULL)) as above4,
        (SELECT company_id FROM gl_company) as company_id
    FROM (
                    SELECT aea.zone_1 area
                      ,aa.acct_num num
                      ,aa.acct_name name
                        ,count(x.customer_id) a_count
                    FROM ar_account aa
                    INNER JOIN ar_entity_address aea
                          ON aa.acct_addr = aea.address_id
                    LEFT JOIN (SELECT customer_id FROM ubs_invoices_payments_v WHERE ifnull(sum_pay,0) != ifnull(invoice_total,0)) x ON x.customer_id = aa.account_id
                    WHERE aa.enable_flag = 'Y' AND aa.status ='ACT'
                    AND LAST_DAY(aa.active_start)
                      BETWEEN LAST_DAY(aa.active_start)
                      AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
                      GROUP BY aea.zone_1,aa.acct_num,aa.acct_name 
                  ) tbl
                  WHERE a_count > 1
                  GROUP BY tbl.area
                  ORDER BY tbl.a_count ASC";

    $query = $this->db->query($sql);
    return $query->result();
    }

    public function get_cash_flow(){
      $sql = "SELECT  sum(payment_amt) amt 
              ,DATE_FORMAT(receipt_date,'%Y-%m') ym
                  ,(SELECT company_id FROM gl_company WHERE company_id = '".$this->session->userdata('company_id')."') company_id 
               FROM ar_receipt   
              GROUP BY DATE_FORMAT(receipt_date,'%Y-%m')";

      $query = $this->db->query($sql);
      return $query->result();
    }



    public function sync_bills($data) {
    $devmark;
    if($data['sp_reason'] == 'DF'){
      $devmark = "Defective Meter";
    }else if($data['sp_reason'] == 'MIA'){
      $devmark = "Meter In-Accesable";
    }else if($data['sp_reason'] == 'ACTREADS'){
      $devmark = "Actual Reading";
    }else{
      $devmark = " ";
    }
    $data3 = array(
        ':p_account_id'    => $data['account_id']
       ,':p_prev_read'     => $data['prev_read']
       ,':p_prev_date'     => $data['prev_date']
       ,':p_meter_reading' => $data['meter_reading']
       ,':p_reading_date'  => $data['reading_date']
       ,':p_consumed'      => $data['consumed']
       ,':p_bill_date'     => $data['bill_date']
       ,':p_bill_month'    => $data['bill_month']
       ,':p_bill_year'     => $data['bill_year']
       ,':p_read_amt'      => $data['read_amt']
       ,':p_bill_bal'      => $data['bill_bal']
       ,':p_total_due'     => $data['total_due']
       ,':p_curchg'        => $data['curchg']
       ,':p_adv_pay'       => $data['adv_pay']
       ,':p_due_date'      => $data['due_date']
       ,':p_print_flag'    => $data['print_flag']
       ,':p_print_by'      => $data['print_by']
       ,':p_print_date'    => $data['print_date']
       ,':p_sp_flag'       => $data['sp_flag']
       ,':p_sp_readType'   => $data['sp_readType']
       ,':p_sp_reason'     => $devmark
       ,':p_max_rate'      => $data['rates']
       ,':p_remarks'       => $data['remarks']
       ,':p_findings'      => $data['findings']
    );

    $this->db->query('call ubs_water_readings_ins(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,@a,@b,@c)', $data3);
    $result = $this->db->query('SELECT @a as status');
    $status;
    $inv;
    foreach($result->result() as $key){
      $status = $key->status;
    }
    return $result->result();
  }




}
